-- @author lixiyong01
-- @date 2023.03.18
-- 针对表中列的备注进行检查
-- 触发表规范第5条：https://docs.qq.com/sheet/DSFFCYVNiUVR6b3JC?tab=orkf1u

use hdp_lbg_supin_zplisting;

insert overwrite table ads_zp_rock_husky_column_check partition(dt = '${dateSuffix}')
select a.table_id, b.db_name as database_name,
    b.tbl_name as table_name,
    b.user_name as owner_id,
    b.lifecycle,
    b.security_level,
    b.create_time,
    a.invalid_columns
from (
    select
        table_id, concat_ws(',',collect_set(`column_name`)) as invalid_columns
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_column
    where dt='${#date(0, 0, -1):yyyyMMdd#}'
        and table_owner in ('xuhualei','wanglizhou','guopeng09','lvruichao','yangxing04',
            'jihailong','yulei12','shaodongyang','zhengbo05','tongdanyu','wuguangxi','liyongqiang06','zhaoqian26')
        and `column_name` not in('dt','id','mt')
        -- and table_name not like 'view_%'
        -- and table_name not like 'vw_%'
        and table_name not like 'tbd_%'
        and table_name not like 'tmp_%'
        -- and table_name not like 'temp_%'
        and table_name not like '%_bak'
        and (length(column_desc)<2 and length(column_cn_name)<2)
    group by table_id, table_owner, table_db_name, table_name
) a
left join (
    select table_id, db_name, tbl_name, tbl_type, user_name, lifecycle, security_level, create_time
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
    where dt='${#date(0, 0, -1):yyyyMMdd#}'
) b on a.table_id = b.table_id
where b.create_time > '2023-12-01' and b.tbl_type!='VIRTUAL_VIEW'
;
